package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import model.Aluno;

public class AlunoDaoImpl implements AlunoDao {
	
	Connection c;
	public AlunoDaoImpl(){
		GenericDao gDao = new GenericDaoImpl();
		c = gDao.getConnection();
	}

	@Override
	public void InsereAluno(Aluno aluno) throws SQLException {
		Aluno a = new Aluno();
		a = ConsultaAluno(aluno);
		if (a.getNome() == null){
			String sql = "INSERT INTO aluno (ra, nome) VALUES (?,?)";
			PreparedStatement ps = c.prepareStatement(sql);
			ps.setString(1, aluno.getRa());
			ps.setString(2, aluno.getNome());
			ps.execute();
			ps.close();
		}
	}
	
	@Override
	public void EditaAluno(Aluno aluno, String raAntigo) throws SQLException {
		String sql = "UPDATE aluno SET ra = ?, nome = UPPER(?) WHERE ra = ?";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setString(1, aluno.getRa());
		ps.setString(2, aluno.getNome());
		ps.setString(3, raAntigo);
		ps.execute();
		ps.close();		
	}

	@Override
	public void RemoveAluno(Aluno aluno) throws SQLException {
		String sql = "DELETE aluno WHERE ra = ?";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setString(1, aluno.getRa());
		ps.execute();
		ps.close();		
	}

	@Override
	public Aluno ConsultaAluno(Aluno aluno) throws SQLException {
		Aluno a = new Aluno();
		String sql = "SELECT ra, nome FROM aluno WHERE ra = ?";
		PreparedStatement ps = c.prepareStatement(sql);
		ps.setString(1, aluno.getRa());
		ResultSet rs = ps.executeQuery();
		if (rs.next()){
			a.setRa(rs.getString("ra"));
			a.setNome(rs.getString("nome"));
		}
		rs.close();
		ps.close();
		return a;
	}

	@Override
	public List<Aluno> ConsultaListaAluno() throws SQLException {
		List<Aluno> lista = new ArrayList<Aluno>();
		String sql = "SELECT ra, nome FROM aluno ORDER BY nome";
		PreparedStatement ps = c.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		while (rs.next()){
			Aluno a = new Aluno();
			a.setRa(rs.getString("ra"));
			a.setNome(rs.getString("nome"));
			lista.add(a);
		}
		rs.close();
		ps.close();
		return lista;
	}

}
